#***********************************************************************************
# **  文件名称：fdm_order_info.sh
# **   功能描述: 把数据从bdm层抽取到fdm层fdm_order_info表
# **    创建者：**
# **   修改日期 ：19-06-13
# **    修改人：
# **    修改内容：
#**********************************************************************************
#!/usr/bin/env bash

DAT=`date -d '-1 day' "+%Y-%m-%d"`
sysdate=` date "+%Y-%m-%d" `

#如果有参数传入，重跑脚本时使用（6-13号跑06-09的任务时）
#$1代表执行脚本时，传入的第一个参数 $n,第n个参数

if [ $1 ]; then
  DAT=$1
  fi
  echo "${DAT}"

  #from_unixtime(unix_timestamp('2017-04-13 21:44:29.0')),

  SQL="
  alter table fdm.mmall_order add if not exists partition (day='"${DAT}"');
  alter table fdm.mmall_order_item add if not exists partition (day='"${DAT}"');
  alter table fdm.mmall_shipping add if not exists partition (day='"${DAT}"');

  insert overwrite table fdm.fdm_order_info partition(day='"${DAT}"')
  select
  o.id int,
  o.order_no,
  o.user_id,
  o.shipping_id,
  o.payment,
  o.payment_type,
  o.postage,
  o.status,

  from_unixtime(unix_timestamp(o.payment_time)) payment_time,
  from_unixtime(unix_timestamp(o.send_time)) send_time,
  from_unixtime(unix_timestamp(o.end_time))end_time,
  from_unixtime(unix_timestamp(o.close_time)) close_time,
  from_unixtime(unix_timestamp(o.create_time))create_time,
  from_unixtime(unix_timestamp(o.update_time))update_time,

  oi.product_id,
  oi.product_name,
  oi.product_image,
  oi.current_unit_price,
  oi.quantity,
  oi.total_price,

  s.receiver_name,
  s.receiver_phone,
  s.receiver_mobile,
  s.receiver_province,
  s.receiver_city,
  s.receiver_district,
  s.receiver_address,
  s.receiver_zip

  from fdm.mmall_order as  o left join fdm.mmall_order_item as oi
  on (o.order_no = oi.order_no)
  left join fdm.mmall_shipping as s
 on (o.user_id = oi.user_id)
 where o.day='"${DAT}"';
 "

 echo "${SQL}"

 hive -e "${SQL}"